/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import xmlobject.DTO.Users.User;
import xmlobject.DTO.Users.Users;

/**
 *
 * @author minhsong
 */
public class UserDAL extends Dataconection {

    private User ReadUserFromDS(ResultSet rs) {
        User result = new User();
        if (rs != null) {
            try {
                result.setUsername(rs.getString("Username"));
                result.setPassword(rs.getString("Password"));
                result.setRoleID(rs.getLong("RoleID"));
                result.setPhoneNumber(rs.getString("PhoneNumber"));
                result.setFullName(rs.getString("FullName"));
                result.setAddress(rs.getString("Address"));
                result.setBirthDate(rs.getDate("BirthDate"));
            } catch (SQLException ex) {
                Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result;
    }

    public User getUserById(String username) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spuserGet(?) }");
            proc_stmt.setString("Username", username);
            rs = proc_stmt.executeQuery();
            if (rs.next()) {
                return ReadUserFromDS(rs);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Users getAllUser() {
        Users result = new Users();
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spuserGetList() }");
            rs = proc_stmt.executeQuery();
            while (rs.next()) {
                result.getUser().add(ReadUserFromDS(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public Users getAllUsersByRoleId(int id) {
        Users result = new Users();
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spuserSelectAllByRoleID(?) }");
            proc_stmt.setLong("RoleID", id);
            rs = proc_stmt.executeQuery();
            while (rs.next()) {
                result.getUser().add(ReadUserFromDS(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public User Create(User value) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spuserInsert(?,?,?,?,?,?,?) }");
            proc_stmt.setString("Username", value.getUsername());
            proc_stmt.setString("Password", value.getPassword());
            proc_stmt.setLong("RoleID", value.getRoleID());
            proc_stmt.setString("PhoneNumber", value.getPhoneNumber());
            proc_stmt.setString("FullName", value.getFullName());
            proc_stmt.setString("Address", value.getAddress());
            proc_stmt.setDate("BirthDate", new java.sql.Date(value.getBirthDate().getYear(), value.getBirthDate().getMonth(), value.getBirthDate().getDay()));
            proc_stmt.execute();
            return value;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public boolean Edit(User value) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spuserUpdate(?,?,?,?,?,?) }");
            proc_stmt.setString("Username", value.getUsername());
            proc_stmt.setLong("RoleID", value.getRoleID());
            proc_stmt.setString("PhoneNumber", value.getPhoneNumber());
            proc_stmt.setString("FullName", value.getFullName());
            proc_stmt.setString("Address", value.getAddress());
            proc_stmt.setDate("BirthDate", new java.sql.Date(value.getBirthDate().getYear(), value.getBirthDate().getMonth(), value.getBirthDate().getDay()));
            return proc_stmt.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean Delete(User value) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spuserDelete(?) }");
            proc_stmt.setString("Username", value.getUsername());
            return proc_stmt.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public Users Search(String username, String fullname) {
        Users result = new Users();
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spuserSearch(?,?) }");
            proc_stmt.setString("Username", username);
            proc_stmt.setString("Fullname", fullname);
            rs = proc_stmt.executeQuery();
            while (rs.next()) {
                result.getUser().add(ReadUserFromDS(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public User Loging(String username, String password) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spuserLogin(?,?) }");
            proc_stmt.setString("Username", username);
            proc_stmt.setString("Password", password);
            rs = proc_stmt.executeQuery();
            if (rs.next()) {
                return ReadUserFromDS(rs);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
}
